package com.example.a828502.sqliteimporttest;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.util.Log;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by 828502 on 2016/4/20.
 */

public class DBManager {

    private final int BUFFER_SIZE = 400000;
//    public static final String DB_NAME = "city_dict.db"; //保存的数据库文件名

    public static final String DB_NAME = "select_data.db"; //保存的数据库文件名


    public static final String PACKAGE_NAME = "com.example.a828502.sqliteimporttest";
    public static final String DB_PATH = "/data"
            + Environment.getDataDirectory().getAbsolutePath() + "/"
            + PACKAGE_NAME;  //在手机里存放数据库的位置(/data/data/com.example.a828502.sqliteimporttest/city_dict.db)

    private DatabaseHelper helper;
    private SQLiteDatabase db;
    private Context context;


    public DBManager(Context context) {
        this.context = context;

        helper = new DatabaseHelper(context);
        // 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0,
        // mFactory);
        // 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
        db = helper.getWritableDatabase();

    }

    public SQLiteDatabase getDatabase() {
        return db;
    }

    public void setDatabase(SQLiteDatabase database) {
        this.db = database;
    }

    public void openData() {
        System.out.println(DB_PATH + "/" + DB_NAME);
        this.db = this.openDatabase(DB_PATH + "/" + DB_NAME);
    }

    private SQLiteDatabase openDatabase(String dbfile) {

        try {
            if (!(new File(dbfile).exists())) {
                //判断数据库文件是否存在，若不存在则执行导入，否则直接打开数据库
                InputStream is = this.context.getResources().openRawResource(
                        R.raw.select_data); //欲导入的数据库（选项数据库）

//                //判断数据库文件是否存在，若不存在则执行导入，否则直接打开数据库
//                InputStream is = this.context.getResources().openRawResource(
//                        R.raw.city_dict); //欲导入的数据库（城市数据库）
                FileOutputStream fos = new FileOutputStream(dbfile);
                byte[] buffer = new byte[BUFFER_SIZE];
                int count = 0;
                while ((count = is.read(buffer)) > 0) {
                    fos.write(buffer, 0, count);
                }
                fos.close();
                is.close();
            }

            SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbfile,null);
            return db;

        } catch (FileNotFoundException e) {
            Log.e("Database", "File not found");
            e.printStackTrace();
        } catch (IOException e) {
            Log.e("Database", "IO exception");
            e.printStackTrace();
        }
        return null;
    }

    /**
     * add
     * @param citys
     */
    public void add(List<CityBean> citys)
    {
        Log.d("Hello DB", "DBManager --> add");
        // 采用事务处理，确保数据完整性
        db.beginTransaction(); // 开始事务
        try
        {
            for (CityBean city : citys)
            {
                db.execSQL("INSERT INTO " + DatabaseHelper.TABLE_NAME
                        + " VALUES(null,null, ?, ?,null,null,null)", new Object[] { city.dist_cn_name,
                        city.dist_code});
                // 带两个参数的execSQL()方法，采用占位符参数？，把参数值放在后面，顺序对应
                // 一个参数的execSQL()方法中，用户输入特殊字符时需要转义
                // 使用占位符有效区分了这种情况
            }
            db.setTransactionSuccessful(); // 设置事务成功完成
        }
        finally
        {
            db.endTransaction(); // 结束事务
        }
    }

    public void addData(List<SelectData> datas)
    {
        Log.d("Hello DB", "DBManager --> add");
        // 采用事务处理，确保数据完整性
        db.beginTransaction(); // 开始事务
        try
        {
            for (SelectData data : datas)
            {
                db.execSQL("INSERT INTO " + DatabaseHelper.TABLE_NAME
                        + " VALUES(?,?, null)", new Object[] { data.select_id,data.select_name
                        });
                // 带两个参数的execSQL()方法，采用占位符参数？，把参数值放在后面，顺序对应
                // 一个参数的execSQL()方法中，用户输入特殊字符时需要转义
                // 使用占位符有效区分了这种情况
            }
            db.setTransactionSuccessful(); // 设置事务成功完成
        }
        finally
        {
            db.endTransaction(); // 结束事务
        }
    }


    /**
     * 更新信息
     *
     */
    public void updateCity(CityBean city)
    {
        ContentValues cv = new ContentValues();
        cv.put("dist_cn_name", city.dist_cn_name);
        cv.put("dist_code",city.dist_code);
        db.update(DatabaseHelper.TABLE_NAME, cv, "dist_cn_name = ?",
                new String[] { city.dist_cn_name });
    }

    public void updateData(SelectData data)
    {
        ContentValues cv = new ContentValues();
        cv.put("select_id", data.select_id);
        cv.put("select_name",data.select_name);
        db.update(DatabaseHelper.TABLE_NAME, cv, "select_name = ?",
                new String[] { data.select_name });
    }

    /**
     * delete
     */
    public void deleteAll(CityBean city)
    {
//        db.delete(DatabaseHelper.java.TABLE_NAME, null,
//                null);
        db.delete(DatabaseHelper.TABLE_NAME, "dist_cn_name = ?",
                new String[] { String.valueOf(city.dist_cn_name) });
    }

    public void deleteAllData(SelectData data)
    {
//        db.delete(DatabaseHelper.java.TABLE_NAME, null,
//                null);
        db.delete(DatabaseHelper.TABLE_NAME, "select_name = ?",
                new String[] { String.valueOf(data.select_name) });
    }

    /**
     * query all persons, return list
     *
     * @return List<Person>
     */
    public List<CityBean> query()
    {
        Log.d("Hello DB", "DBManager --> query");
        ArrayList<CityBean> citys = new ArrayList<CityBean>();
        Cursor c = queryTheCursor();
        while (c.moveToNext())
        {
            CityBean city = new CityBean();
            city.id = c.getInt(c.getColumnIndex("id"));
            city.dist_cn_name = c.getString(c.getColumnIndex("dist_cn_name"));
            city.dist_code = c.getString(c.getColumnIndex("dist_code"));
            citys.add(city);
        }
        c.close();
        return citys;
    }


    /**
     * query all persons, return list
     *
     * @return List<Person>
     */
    public List<SelectData> querySelectData()
    {
        Log.d("Hello DB", "DBManager --> query");
        ArrayList<SelectData> selectDatas = new ArrayList<SelectData>();
        Cursor c = queryTheCursor();
        while (c.moveToNext())
        {
            SelectData selectData = new SelectData();
            selectData.id = c.getInt(c.getColumnIndex("id"));
            selectData.select_id = c.getString(c.getColumnIndex("select_id"));
            selectData.select_name = c.getString(c.getColumnIndex("select_name"));
            selectDatas.add(selectData);
        }
        c.close();
        return selectDatas;
    }


    public List<CityBean> queryCursor()
    {
        Log.d("Hello DB", "DBManager --> query");
        ArrayList<CityBean> citys = new ArrayList<CityBean>();
//        Cursor c = queryTheCursor();
        Cursor mCursor=db.query(DatabaseHelper.TABLE_NAME,
                new String[]{"id as id","dist_cn_name","dist_code"},"parent_dist_code = ?", new String[]{"A410000000"}, null, null, null);

        while (mCursor.moveToNext())
        {
            CityBean city = new CityBean();
//            city.id = mCursor.getInt(mCursor.getColumnIndex("id"));
            city.dist_cn_name = mCursor.getString(mCursor.getColumnIndex("dist_cn_name"));
            city.dist_code = mCursor.getString(mCursor.getColumnIndex("dist_code"));
            citys.add(city);
        }
        mCursor.close();
        return citys;
    }

    /**
     * query all persons, return cursor
     *
     * @return Cursor
     */
    public Cursor queryTheCursor()
    {
        Log.d("Hello DB", "DBManager --> queryTheCursor");
        Cursor c = db.rawQuery("SELECT * FROM " + DatabaseHelper.TABLE_NAME,
                null);
        return c;
    }
    public void closeDatabase() {
        this.db.close();

    }
}